
CREATE DATABASE GuiTietKiem
GO
USE GuiTietKiem
GO

/*===============================Tbl.QUYEN===============================================================*/

CREATE TABLE QUYEN
(
	MaQuyen		VARCHAR (10)	NOT NULL	PRIMARY KEY,
	TenQuyen	NVARCHAR (30)	NOT NULL
)
GO
INSERT INTO QUYEN VALUES('MQ001','Manager')
INSERT INTO QUYEN VALUES('MQ002','Users')
INSERT INTO QUYEN VALUES('MQ003','Admin')



/*===============================Tbl.NHOM_NGUOI_DUNG===============================================================*/
GO

CREATE TABLE NHOM_NGUOI_DUNG
(
	MaNhomNguoiDung		VARCHAR (10)		NOT NULL	PRIMARY KEY,
	TenNhomNguoiDung	NVARCHAR (30)	NOT NULL
)

INSERT INTO NHOM_NGUOI_DUNG VALUES('PHGKT','phong ke toan')
INSERT INTO NHOM_NGUOI_DUNG VALUES('PHGTD','phong tin dung')
INSERT INTO NHOM_NGUOI_DUNG VALUES('PHGTT','phong cong nghe thong tin')
INSERT INTO NHOM_NGUOI_DUNG VALUES('PHGCC','ban dieu hanh')


/*===============================Tbl.PHONG_GIAO_DICH===============================================================*/
GO

CREATE TABLE PHONG_GIAO_DICH
(
	MaPhongGiaoDich	VARCHAR (10)	NOT NULL	PRIMARY KEY,
	TenPhong NVARCHAR (255)		NOT NULL,
	DiaDiem	NVARCHAR (255)		NOT NULL, 
	DienThoai	VARCHAR (20)	
)

INSERT INTO PHONG_GIAO_DICH VALUES('PGDHCM','Chi nhanh Ho Chi Minh','248 Tran Hung Dao','08.3841706')
INSERT INTO PHONG_GIAO_DICH VALUES('PGDHN','Chi nhanh Ha Noi','123 Trung Nu Vuong','04.3550484')



/*===============================Tbl.QUAY_GIAO_DICH===============================================================*/
GO

CREATE TABLE QUAY_GIAO_DICH
(
	MaPhongGiaoDich	VARCHAR (10)		NOT NULL,
	MaQuayGiaoDich	VARCHAR (10)		NOT NULL	
		CONSTRAINT P_QUAY_GIAO_DICH PRIMARY KEY(MaQuayGiaoDich),
		CONSTRAINT F_QUAY_GIAO_DICH FOREIGN KEY(MaPhongGiaoDich) REFERENCES PHONG_GIAO_DICH(MaPhongGiaoDich) 		
)

INSERT INTO QUAY_GIAO_DICH VALUES('PGDHCM','MQLX1')
INSERT INTO QUAY_GIAO_DICH VALUES('PGDHN','MQLX2')
INSERT INTO QUAY_GIAO_DICH VALUES('PGDHN','MQLX3')
INSERT INTO QUAY_GIAO_DICH VALUES('PGDHCM','MQLX4')
INSERT INTO QUAY_GIAO_DICH VALUES('PGDHCM','MQAP1')
INSERT INTO QUAY_GIAO_DICH VALUES('PGDHCM','MQAP2')


/*===============================Tbl5.NHAN_VIEN===============================================================*/
GO

CREATE TABLE NHAN_VIEN
(
	MaNV	VARCHAR (10) NOT NULL PRIMARY KEY,
	HoTen	NVARCHAR (50) NOT NULL,
	NgaySinh DATETIME,
	DiaChi	NVARCHAR (50) NOT NULL,
	DienThoai	VARCHAR (20),
	MaPhongGiaoDich	VARCHAR (10) NOT NULL
		CONSTRAINT F1_NHAN_VIEN FOREIGN KEY(MaPhongGiaoDich) REFERENCES PHONG_GIAO_DICH(MaPhongGiaoDich) 		
)	

INSERT INTO NHAN_VIEN VALUES('MM000001','Nguyen Tan Phat','24/04/1987','HCM','0909835049','PGDHCM')
INSERT INTO NHAN_VIEN VALUES('MM000002','Nguyen Thi Hong Hoa','12/10/1989','HCM','0909710369','PGDHCM')
INSERT INTO NHAN_VIEN VALUES('MM000003','Pham Le Minh Tri','1/1/1990','HN','01685555978','PGDHN')



/*===============================Tbl6.GIAO_DICH_VIEN===============================================================*/
GO

CREATE TABLE GIAO_DICH_VIEN
(
	MaNV	VARCHAR (10) NOT NULL PRIMARY KEY,
	HanMucthu	FLOAT,
	HanMucChi	FLOAT,
	HanMucTonQuy	FLOAT

	CONSTRAINT F1_GIAO_DICH_VIEN FOREIGN KEY(MaNV) REFERENCES NHAN_VIEN(MaNV)
)	

INSERT INTO GIAO_DICH_VIEN VALUES('MM000001',200.000000,200.000000,300.000000)
INSERT INTO GIAO_DICH_VIEN VALUES('MM000002',200.000000,200.000000,300.000000)
INSERT INTO GIAO_DICH_VIEN VALUES('MM000003',200.000000,200.000000,300.000000)



/*===============================Tbl7.PHAN_NHOM===============================================================*/

GO

CREATE TABLE PHAN_NHOM
(
	MaNV	VARCHAR (10) NOT NULL,
	MaNhomNguoiDung		VARCHAR (10)	NOT NULL
		CONSTRAINT P_PHAN_NHOM PRIMARY KEY(MaNV,MaNhomNguoiDung),
		CONSTRAINT F1_PHAN_NHOM FOREIGN KEY(MaNV) REFERENCES NHAN_VIEN(MaNV),
		CONSTRAINT F2_PHAN_NHOM FOREIGN KEY(MaNhomNguoiDung) REFERENCES NHOM_NGUOI_DUNG(MaNhomNguoiDung)
)

INSERT INTO PHAN_NHOM VALUES('MM000001','PHGCC')
INSERT INTO PHAN_NHOM VALUES('MM000002','PHGCC')
INSERT INTO PHAN_NHOM VALUES('MM000003','PHGKT')

/*===============================Tbl7.PHAN_QUYEN===============================================================*/

GO

CREATE TABLE PHAN_QUYEN
(
	MaQuyen		VARCHAR (10)		NOT NULL,
	MaNhomNguoiDung VARCHAR (10)	NOT NULL,  
	MaNV	VARCHAR (10) NOT NULL,
	MatKhau	VARCHAR (30) NOT NULL,
	TenDangNhap	NVARCHAR (50) NOT NULL 
		CONSTRAINT P_PHAN_QUYEN PRIMARY KEY(MaQuyen,MaNhomNguoiDung),
		CONSTRAINT F_PHAN_QUYEN FOREIGN KEY(MaQuyen) REFERENCES QUYEN(MaQuyen),
		CONSTRAINT F1_PHAN_QUYEN FOREIGN KEY(MaNV) REFERENCES NHAN_VIEN(MaNV)
		
)

INSERT INTO PHAN_QUYEN VALUES('MQ001','PHGCC','MM000001','honghoa','honghoa')
INSERT INTO PHAN_QUYEN VALUES('MQ002','PHGKT','MM000006','minhtri','minhtri')
INSERT INTO PHAN_QUYEN VALUES('MQ003','PHGTT','MM000011','tanphat','tanphat')

/*===============================Tbl8.NGAY_GIAO_DICH===============================================================*/

GO

CREATE TABLE NGAY_GIAO_DICH
(
	NgayGiaoDich DATETIME 	NOT NULL PRIMARY KEY,
	NgatGiaoDich BIT 
) 

INSERT INTO NGAY_GIAO_DICH VALUES('02/02/2011',0)
INSERT INTO NGAY_GIAO_DICH VALUES('03/02/2011',0)
INSERT INTO NGAY_GIAO_DICH VALUES('02/08/2011',0)

/*DELETE NGAY_GIAO_DICH
*/

/*===============================Tbl9.KHACH_HANG===============================================================*/

GO

CREATE TABLE KHACH_HANG
(
	MaKH	VARCHAR (10) NOT NULL PRIMARY KEY,
	HoTen	NVARCHAR (50) NOT NULL,
	CMND	VARCHAR (9) NOT NULL,
	NgayCap DATETIME,
	NoiCap	NVARCHAR (50) NOT NULL, 
	DiaChi	NVARCHAR (50) NOT NULL,
	DienThoai	VARCHAR (12),
)

INSERT INTO KHACH_HANG VALUES('KH000001','Truong Ho Vu','351657341','12/22/2002','CA.HCM','Tan Binh','0909090909')
INSERT INTO KHACH_HANG VALUES('KH000002','Truong Ngoc Trinh','351658361','12/22/2004','CA.HCM','Tan phu','0985633704')
INSERT INTO KHACH_HANG VALUES('KH000003','Duong Kim Thanh','351658371','12/24/2004','CA.HCM','Binh Thanh','0945633702')


/*===============================Tbl10.DM_TAI_KHOAN===============================================================*/

GO

CREATE TABLE DM_TAI_KHOAN
(
	SoHieuTaiKhoan	VARCHAR (20) NOT NULL PRIMARY KEY,
	TenTaiKhoan	NVARCHAR (255) NOT NULL,
	TinhChatSoDu	VARCHAR (5) 
) 

INSERT INTO DM_TAI_KHOAN VALUES ('101100','Tien mat bang dong VN tai don vi','')
INSERT INTO DM_TAI_KHOAN VALUES ('103100','Tien mat ngoai te tai don vi','')
INSERT INTO DM_TAI_KHOAN VALUES ('423100','Tien gui tiet kiem khong ky han bang dong VN','')
INSERT INTO DM_TAI_KHOAN VALUES ('423200','Tien gui tiet kiem co ky han bang dong VN','')



/*===============================Tbl12.DM_LOAI_TIEN===============================================================*/

GO

CREATE TABLE DM_LOAI_TIEN
(
	MaLoaiTien	VARCHAR (10) NOT NULL PRIMARY KEY,
	LoaiTien	VARCHAR (20) NOT NULL,
) 

INSERT INTO DM_LOAI_TIEN VALUES ('0000','VND')
INSERT INTO DM_LOAI_TIEN VALUES ('0037','USD')
INSERT INTO DM_LOAI_TIEN VALUES ('0014','EUR')


/*===============================Tbl13.DM_LOAI_GIAO_DICH===============================================================*/

GO

CREATE TABLE DM_LOAI_GIAO_DICH
(
	MaLoaiGiaoDich	VARCHAR (10) NOT NULL PRIMARY KEY,
	TenGiaoDich	NVARCHAR (50) NOT NULL,
) 

INSERT INTO DM_LOAI_GIAO_DICH VALUES ('GDTK0001','Mo so gui von co ky han')
INSERT INTO DM_LOAI_GIAO_DICH VALUES ('GDTK0002','Mo so gui von khong ky han')
INSERT INTO DM_LOAI_GIAO_DICH VALUES ('GDTK0003','Rut lai so co ky han')
INSERT INTO DM_LOAI_GIAO_DICH VALUES ('GDTK0004','Rut lai so khong ky han')
INSERT INTO DM_LOAI_GIAO_DICH VALUES ('GDTK0005','Rut von so khong ky han')
INSERT INTO DM_LOAI_GIAO_DICH VALUES ('GDTK0006','Tat toan so')

/*===============================Tbl14.DM_KY_LAI_TIET_KIEM===============================================================*/

GO

CREATE TABLE DM_KY_LAI_TIET_KIEM
(
	MaKyTinhLai	VARCHAR (10) NOT NULL PRIMARY KEY,
	LoaiKyTinhLai	NVARCHAR (50) NOT NULL,
) 

INSERT INTO DM_KY_LAI_TIET_KIEM VALUES ('KTLT0001','Hang thang')
INSERT INTO DM_KY_LAI_TIET_KIEM VALUES ('KTLQ0002','Hang quy')
INSERT INTO DM_KY_LAI_TIET_KIEM VALUES ('KTLN0003','Hang nam')


/*===============================Tbl15.LOAI_TIET_KIEM===============================================================*/

GO

CREATE TABLE LOAI_TIET_KIEM
(
	MaLoaiTietKiem	VARCHAR (10) NOT NULL PRIMARY KEY,
	TenLoaiTietKiem	NVARCHAR (255) NOT NULL,
	KyHan	INT,
	DangHuyDong BIT,
	MaLoaiTien	VARCHAR (10) NOT NULL,
	MaKyTinhLai	VARCHAR (10) NOT NULL,
	SoHieuTaiKhoan	VARCHAR (20) NOT NULL	
		CONSTRAINT F_LOAI_TIET_KIEM FOREIGN KEY(MaLoaiTien) REFERENCES DM_LOAI_TIEN(MaLoaiTien), 	 	
		CONSTRAINT F1_LOAI_TIET_KIEM FOREIGN KEY(MaKyTinhLai) REFERENCES DM_KY_LAI_TIET_KIEM(MaKyTinhLai), 	 	
		CONSTRAINT F2_LOAI_TIET_KIEM FOREIGN KEY(SoHieuTaiKhoan) REFERENCES DM_TAI_KHOAN(SoHieuTaiKhoan) 	 	
) 

INSERT INTO LOAI_TIET_KIEM VALUES ('TK423100','TK KKH VND tinh lai cuoi thang, nhap lai cuoi thang',0,1,'0000','KTLT0001','423100')
INSERT INTO LOAI_TIET_KIEM VALUES ('TK423203','TK CKH VND 3 thang, tra lai 3 thang, lai tra sau',3,1,'0000','KTLT0001','423203')
INSERT INTO LOAI_TIET_KIEM VALUES ('TK423206','TK CKH VND 6 thang, tra lai 6 thang',6,1,'0000','KTLT0001','423206')




/*===============================Tbl16.SO_TIET_KIEM===============================================================*/

GO

CREATE TABLE SO_TIET_KIEM
(
	MaLoaiTietKiem	VARCHAR (10) NOT NULL,
	MaSoTietKiem	VARCHAR (10) NOT NULL,	
	NgayMoSo	DATETIME,
	NgayHieuLuc DATETIME,
	NgayDenHan	DATETIME,
	NgayTinhLaiGanNhat	DATETIME,
	NgayTaiKyGui	DATETIME,	
	LaiDaTra	FLOAT,
	LaiNhapVon	FLOAT,	
	NgayNhapVon	DATETIME,
	PhongToa BIT,
	NgayTatToan DATETIME,
	MaKH	VARCHAR (10) NOT NULL 

	CONSTRAINT P_SO_TIET_KIEM PRIMARY KEY(MaSoTietKiem),
	CONSTRAINT F_SO_TIET_KIEM FOREIGN KEY(MaLoaiTietKiem) REFERENCES LOAI_TIET_KIEM(MaLoaiTietKiem), 	 	
	CONSTRAINT F1_SO_TIET_KIEM FOREIGN KEY(MaKH) REFERENCES KHACH_HANG(MaKH)	 	
) 

INSERT INTO SO_TIET_KIEM VALUES ('TK423100','STK000001','02/02/2009','02/02/2009','02/20/2009','02/10/2009','',0,0,'',0,'02/20/2009','KH000001')
INSERT INTO SO_TIET_KIEM VALUES ('TK423203','STK000002','02/01/2009','02/01/2009','02/02/2009','02/02/2009','02/02/2009',0,0,'02/02/2009',0,'02/02/2009','KH000001')
INSERT INTO SO_TIET_KIEM VALUES ('TK423206','STK000003','03/02/2009','03/02/2009','05/02/2009','02/04/2009','02/04/2009',0,0,'02/04/2009',0,'02/04/2009','KH000003')




/*===============================Tbl17.LAI_SUAT_TIET_KIEM===============================================================*/

GO

CREATE TABLE LAI_SUAT_TIET_KIEM
(
	MaLoaiTietKiem	VARCHAR (10) NOT NULL,
	Ngay	DATETIME NOT NULL,
	MucLaiSuat	FLOAT
		CONSTRAINT P_LAI_SUAT_TIET_KIEM PRIMARY KEY(MaLoaiTietKiem,Ngay),
		CONSTRAINT F1_LAI_SUAT_TIET_KIEM FOREIGN KEY(MaLoaiTietKiem) REFERENCES LOAI_TIET_KIEM(MaLoaiTietKiem) 	 	
) 

INSERT INTO LAI_SUAT_TIET_KIEM VALUES ('TK423100','12/22/2008',0.150)
INSERT INTO LAI_SUAT_TIET_KIEM VALUES ('TK423203','12/22/2008',0.500)
INSERT INTO LAI_SUAT_TIET_KIEM VALUES ('TK423206','12/22/2008',0.550)



/*===============================Tbl18.SO_DU_TIET_KIEM===============================================================*/

GO

CREATE TABLE SO_DU_TIET_KIEM
(
	MaLoaiTietKiem	VARCHAR (10) NOT NULL,
	MaSoTietKiem	VARCHAR (10) NOT NULL,
	NgayCapNhat	DATETIME NOT NULL,
	SoDu	FLOAT
		CONSTRAINT P_SO_DU_TIET_KIEM PRIMARY KEY(MaLoaiTietKiem,MaSoTietKiem,NgayCapNhat),
		CONSTRAINT F1_SO_DU_TIET_KIEM FOREIGN KEY(MaSoTietKiem) REFERENCES SO_TIET_KIEM(MaSoTietKiem), 	 	 	 	
		CONSTRAINT F2_SO_DU_TIET_KIEM FOREIGN KEY(MaLoaiTietKiem) REFERENCES LOAI_TIET_KIEM(MaLoaiTietKiem) 	 	 	 	
) 

INSERT INTO SO_DU_TIET_KIEM VALUES ('TK423100','STK000001','02/01/2009',30000000)
INSERT INTO SO_DU_TIET_KIEM VALUES ('TK423203','STK000004','03/04/2009',500000000)
INSERT INTO SO_DU_TIET_KIEM VALUES ('TK423206','STK000010','02/02/2009',150000000)


/*===============================Tbl19.PS_TIET_KIEM===============================================================*/

GO

CREATE TABLE PS_TIET_KIEM
(
	SoButToan	VARCHAR (10) NOT NULL,
	NgayGiaoDich		DATETIME NOT NULL,
	DaDuyet	BIT,
	MaSoTietKiem	VARCHAR (10) NOT NULL,
	MaLoaiGiaoDich	VARCHAR (10) NOT NULL,
	MaQuayGiaoDich	VARCHAR (10) NOT NULL,
	MaPhongGiaoDich	VARCHAR (10) NOT NULL,
	MaNV	VARCHAR (10) NOT NULL	
	
		CONSTRAINT P_PS_TIET_KIEM PRIMARY KEY(SoButToan,NgayGiaoDich),
		CONSTRAINT F1_PS_TIET_KIEM FOREIGN KEY(MaLoaiGiaoDich) REFERENCES DM_LOAI_GIAO_DICH(MaLoaiGiaoDich),
		CONSTRAINT F2_PS_TIET_KIEM FOREIGN KEY(MaSoTietKiem) REFERENCES SO_TIET_KIEM(MaSoTietKiem),
		CONSTRAINT F3_PS_TIET_KIEM FOREIGN KEY(MaQuayGiaoDich) REFERENCES QUAY_GIAO_DICH(MaQuayGiaoDich),
		CONSTRAINT F4_PS_TIET_KIEM FOREIGN KEY(MaPhongGiaoDich) REFERENCES PHONG_GIAO_DICH(MaPhongGiaoDich),
		CONSTRAINT F5_PS_TIET_KIEM FOREIGN KEY(MaNV) REFERENCES GIAO_DICH_VIEN(MaNV),
		CONSTRAINT F6_PS_TIET_KIEM FOREIGN KEY(NgayGiaoDich) REFERENCES NGAY_GIAO_DICH(NgayGiaoDich) 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	
) 

INSERT INTO PS_TIET_KIEM VALUES ('BT000002','02/02/2009',1,'STK000001','GDTK0001','MQLX2','PGDHCM','MM000001')
INSERT INTO PS_TIET_KIEM VALUES ('BT000001','02/01/2009',1,'STK000002','GDTK0003','MQLX1','PGDHCM','MM000002')
INSERT INTO PS_TIET_KIEM VALUES ('BT000003','03/02/2009',1,'STK000003','GDTK0002','MQLX3','PGDHN','MM000003')




/*===============================Tbl20.CHI_TIET_PSGD===============================================================*/

GO

CREATE TABLE CHI_TIET_PSGD
(
	SoGiaoDich	VARCHAR (10) NOT NULL,
	SoButToan	VARCHAR (10) NOT NULL,
	NgayGiaoDich		DATETIME NOT NULL,
	SoHieuTaiKhoanNo	VARCHAR (20) NOT NULL,
	SoHieuTaiKhoanCo	VARCHAR (20) NOT NULL,
	SoTien	FLOAT,

	CONSTRAINT P_CHI_TIET_PSGD PRIMARY KEY(SoGiaoDich,SoButToan,NgayGiaoDich),
	CONSTRAINT F2_CHI_TIET_PSGD FOREIGN KEY(SoButToan,NgayGiaoDich) REFERENCES PS_TIET_KIEM (SoButToan,NgayGiaoDich), 	 	
	CONSTRAINT F3_CHI_TIET_PSGD FOREIGN KEY(SoHieuTaiKhoanNo) REFERENCES DM_TAI_KHOAN (SoHieuTaiKhoan),
	CONSTRAINT F4_CHI_TIET_PSGD FOREIGN KEY(SoHieuTaiKhoanCo) REFERENCES DM_TAI_KHOAN (SoHieuTaiKhoan)
) 

INSERT INTO CHI_TIET_PSGD VALUES ('GDPS0001','BT000001','02/01/2009','423100','423100',200000000000)
INSERT INTO CHI_TIET_PSGD VALUES ('GDPS0002','BT000002','02/02/2009','101100','101100',30000000)
INSERT INTO CHI_TIET_PSGD VALUES ('GDPS0003','BT000003','03/02/2009','103100','103100',40000000)

